package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.SenateScore;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 教务积分 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-10-26
 */
public interface SenateScoreMapper extends BaseMapper<SenateScore> {

    /**
     * 教务积分列表
     */
    @Select({"<script>",
            "<if test= 'type == 6'>",
                "SELECT t01.school_id,t01.pid AS school_pid,t01.school_name,t.* ",
                "FROM ( SELECT * FROM school_quarter WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND deleted = 0) AS t01  ",
                "LEFT JOIN (",
            "</if>",
                    "SELECT",
                    "t1.id,",
                    "t1.year_part,",
                    "t1.quarter_num,",
                    "t1.lesson_num,",
                    "t1.teacher_id,",
                    "t1.school_id,",
                    "t1.subjects_id,",
                    "t1.projects_id,",
                    "SUM( t1.count ) AS all_count,",
                    "SUM( t1.score ) AS all_score,",
                    "IFNULL(COUNT(DISTINCT t1.teacher_id),0) AS teacher_count,",
                    "getStaffNameForId(t1.teacher_id) AS teacher_name ,",
                    "<if test= 'type != 6'>",
                        "getSchoolName(t1.school_id) AS schoolName,",
                    "</if>",
                    "SUM( CASE WHEN DATE_FORMAT( t1.updated_at, '%Y-%m-%d' ) BETWEEN '${startDate}' AND '${endDate}' THEN score ELSE 0 END ) AS date_score ",
                    "FROM",
                    "senate_score AS t1",
                    "LEFT JOIN staff AS t2 ON t1.teacher_id = t2.id ",
                    "WHERE",
                    "t1.year_part = ${yearPart} ",
                    "AND t1.quarter_num = ${quarterNum}",
                    "<when test='(startLessonNum!=null and startLessonNum!=&apos;&apos;) and (endLessonNum!=null and endLessonNum!=&apos;&apos;) '>",
                         "AND t1.lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum}",
                    "</when>",
                    "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                        "AND t1.subjects_id IN ( ${subjectsId} ) ",
                    "</when>",
                    "<when test='projectsId!=null and projectsId!=&apos;&apos; '>",
                        "AND t1.projects_id IN ( ${projectsId} ) ",
                    "</when>",
//                    "<when test='senateDepartId!=null and senateDepartId!=&apos;&apos; '>",
//                        "AND t1.subjects_id IN ( ${senateDepartId} ) ",
//                    "</when>",
                    "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                        "AND t1.school_id in(${schoolId})",
                    "</when>",
                    "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                        "AND t2.name LIKE '%${teacherName}%' ",
                    "</when>",
                    "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                        "AND t1.year_class_id IN ( ${yearClassId} ) ",
                    "</when>",
                    "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
                        "AND t1.year_class_id IS NULL ",
                    "</when>",
                    "<if test= 'type == 4 or type == 6'>",
                        "AND t1.school_id IS NOT NULL ",
                    "</if>",
                    "<if test= 'type == 1'>",
                        "AND t1.teacher_id IS NOT NULL",
                        "AND t1.type = 1",
                    "</if>",
                    "GROUP BY ",
                    "<if test= 'type == 1 or type == 3'>",
                        "t1.teacher_id",
                    "</if>",
                    "<if test= 'type == 4 or type == 6'>",
                        "t1.school_id",
                    "</if>",
                "<if test= 'type == 6'>",
                ") AS t ON t01.school_id = t.school_id ",
                    "GROUP BY t01.school_id ",
                "</if>",
            "</script>"})
    public List<SenateScore> getSenateScoreList(@Param("yearPart") Integer yearPart,
                                                @Param("quarterNum") Integer quarterNum,
                                                @Param("startLessonNum") String startLessonNum,
                                                @Param("endLessonNum") String endLessonNum,
                                                @Param("subjectsId") String subjectsId,
                                                @Param("projectsId") String projectsId,
                                                @Param("startDate") String startDate,
                                                @Param("endDate") String endDate,
                                                @Param("schoolId") String schoolId,
                                                @Param("teacherName") String teacherName,
                                                @Param("type") String type,
                                                @Param("yearClassId") String yearClassId,
                                                @Param("senateDepartId") String senateDepartId
    );

    @Select({"<script>",
            "SELECT",
            "t1.id,",
            "t1.year_part,",
            "t1.quarter_num,",
            "t1.lesson_num,",
            "t1.teacher_id,",
            "t1.subjects_id,",
            "t1.projects_id,",
            "t1.count,",
            "t1.score,",
            "t1.score_type,",
            "t1.school_id, ",
            "getSchoolName(t1.school_id) AS schoolName,",
            "t2.`name` AS teacher_name,",
            "getDictName(projects_id, 28) as projects_name",
            "FROM",
            "senate_score AS t1",
            "LEFT JOIN staff AS t2 ON t1.teacher_id = t2.id ",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum} ",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                "AND teacher_id = ${teacherId}",
            "</when>",
            "<when test='projectsId!=null and projectsId!=&apos;&apos; '>",
                "AND t1.projects_id IN ( ${projectsId} ) ",
            "</when>",
            "<when test='detailType!=null and detailType==2'>",
                "AND DATE_FORMAT( t1.updated_at, '%Y-%m-%d' ) BETWEEN '${startDate}' AND '${endDate}'",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "AND t1.school_id in(${schoolId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "AND t1.subjects_id IN ( ${subjectsId} ) ",
            "</when>",
            "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                "AND t2.name LIKE '%${teacherName}%' ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                "AND t1.year_class_id IN ( ${yearClassId} ) ",
            "</when>",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
                "AND t1.year_class_id IS NULL ",
            "</when>",
            "</script>"})
    public List<SenateScore> getSenateScoreDetail(@Param("yearPart") Integer yearPart,
                                                  @Param("quarterNum") Integer quarterNum,
                                                  @Param("startLessonNum") Integer startLessonNum,
                                                  @Param("endLessonNum") Integer endLessonNum,
                                                  @Param("teacherId") Long teacherId,
                                                  @Param("subjectsId") String subjectsId,
                                                  @Param("projectsId") String projectsId,
                                                  @Param("detailType") Integer detailType,
                                                  @Param("startDate") String startDate,
                                                  @Param("endDate") String endDate,
                                                  @Param("schoolId") String schoolId,
                                                  @Param("teacherName") String teacherName,
                                                  @Param("yearClassId") String yearClassId,
                                                  @Param("senateDepartId") String senateDepartId
                                                  );


    /**
     * 教务积分战队列表
     */
    @Select({"<script>",
            "SELECT t1.team_id,t1.team_pid,t1.team_name,",
            "IFNULL( SUM( all_score ), 0 ) AS all_score,",
            "IFNULL( SUM( date_score ), 0 ) AS date_score ",
            "FROM (",
                "SELECT ",
                "tm.team_name,tm.id AS team_id,tm.pid AS team_pid,tm.school_id,tm.classify,tm.year_class_id ",
                "FROM ",
                "track_market AS tm ",
                "WHERE ",
                "tm.classify IN ( ${senateDepartId} )",
                "AND tm.track_type = ${trackType} ",
                "AND tm.`status` = 1 ",
                "AND tm.deleted = 0 ",
                "AND tm.year_part = ${yearPart} ",
                "AND tm.quarter_num = ${quarterNum} ",
                "<when test='teamId!=null and teamId!=&apos;&apos; '>",
                    "AND tm.id IN (${teamId}) ",
                "</when>",
                "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
                    "AND tm.pid = 0 ",
                "</when>",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                    "AND ( tm.year_class_id IN ( ${yearClassId} ) OR tm.pid = 0 )",
                "</when>",
                "GROUP BY tm.id",
            ") AS t1 ",
            "LEFT JOIN (",
                "SELECT",
                "t1.id,",
                "t1.year_part,",
                "t1.quarter_num,",
                "t1.lesson_num,",
                "t1.teacher_id,",
                "t1.school_id,",
                "t1.subjects_id,",
                "t1.projects_id,",
                "t2.senate_depart_id,",
                "t1.year_class_id,",
                "SUM( t1.score ) AS all_score,",
                "SUM( CASE WHEN DATE_FORMAT( t1.updated_at, '%Y-%m-%d' ) BETWEEN '${startDate}' AND '${endDate}' THEN score ELSE 0 END ) AS date_score ",
                "FROM",
                "senate_score AS t1",
                "LEFT JOIN senate_depart_projects AS t2 ON t1.projects_id = t2.projects_id",
                "WHERE",
                "t1.year_part = ${yearPart} ",
                "AND t1.quarter_num = ${quarterNum}",
                "<when test='(startLessonNum!=null and startLessonNum!=&apos;&apos;) and (endLessonNum!=null and endLessonNum!=&apos;&apos;) '>",
                    "AND t1.lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum}",
                "</when>",
                "<when test='projectsId!=null and projectsId!=&apos;&apos; '>",
                    "AND t1.projects_id IN ( ${projectsId} ) ",
                "</when>",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                    "AND t1.year_class_id IN ( ${yearClassId} ) ",
                "</when>",
                "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
                    "AND t1.year_class_id IS NULL ",
                "</when>",
                "GROUP BY t1.school_id ,t1.projects_id,t2.senate_depart_id ",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                    ",t1.year_class_id ",
                "</when>",
            ") AS t ON t.school_id = t1.school_id AND t.senate_depart_id = t1.classify ",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                "AND t.year_class_id = t1.year_class_id ",
            "</when>",
            "where 1=1",
            "GROUP BY team_id,classify",
            "</script>"})
    public List<SenateScore> getSenateScoreTeamList(@Param("yearPart") Integer yearPart,
                                                    @Param("quarterNum") Integer quarterNum,
                                                    @Param("startLessonNum") String startLessonNum,
                                                    @Param("endLessonNum") String endLessonNum,
                                                    @Param("subjectsId") String subjectsId,
                                                    @Param("projectsId") String projectsId,
                                                    @Param("startDate") String startDate,
                                                    @Param("endDate") String endDate,
                                                    @Param("senateDepartId") String senateDepartId,
                                                    @Param("trackType") String trackType,
                                                    @Param("teamId") String teamId,
                                                    @Param("yearClassId") String yearClassId
    );

    /**
     * 教务积分战队详情
     */
    @Select({"<script>",
            "SELECT t.* ",
            "FROM (",
                "SELECT ",
                "tm.team_name,tm.id AS team_id,tm.pid AS team_pid,tm.school_id,tm.classify,tm.year_class_id ",
                "FROM ",
                "track_market AS tm ",
                "WHERE ",
                "tm.classify IN ( ${senateDepartId} )",
                "AND tm.track_type = ${trackType} ",
                "AND tm.`status` = 1 ",
                "AND tm.deleted = 0 ",
                "AND tm.year_part = ${yearPart} ",
                "AND tm.quarter_num = ${quarterNum} ",
                "<when test='teamId!=null and teamId!=&apos;&apos; '>",
                    "AND tm.id IN (${teamId}) ",
                "</when>",
                "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
                    "AND tm.pid = 0 ",
                "</when>",
                "GROUP BY tm.id",
            ") AS t1 ",
            "LEFT JOIN (",
                "SELECT",
                "t1.id,",
                "t1.year_part,",
                "t1.quarter_num,",
                "t1.lesson_num,",
                "t1.teacher_id,",
                "t1.subjects_id,",
                "t1.projects_id,",
                "t1.count,",
                "t1.score,",
                "t1.score_type,",
                "t1.school_id, ",
                "t1.year_class_id, ",
                "t2.senate_depart_id,",
                "getDictName( t1.projects_id, 28 ) as projects_name",
                "FROM",
                "senate_score AS t1",
                "LEFT JOIN senate_depart_projects AS t2 ON t1.projects_id = t2.projects_id",
                "WHERE",
                "t1.year_part = ${yearPart} ",
                "AND t1.quarter_num = ${quarterNum} ",
                "AND t1.type = 5 ",
                "AND t1.lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum} ",
                "<when test='projectsId!=null and projectsId!=&apos;&apos; '>",
                    "AND t1.projects_id IN ( ${projectsId} ) ",
                "</when>",
                "<when test='detailType!=null and detailType==2'>",
                    "AND DATE_FORMAT( t1.updated_at, '%Y-%m-%d' ) BETWEEN '${startDate}' AND '${endDate}'",
                "</when>",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                    "AND t1.year_class_id IN ( ${yearClassId} ) ",
                "</when>",
                "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
                    "AND t1.year_class_id IS NULL ",
                "</when>",
            ") AS t ON t.school_id = t1.school_id AND t.senate_depart_id = t1.classify ",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                "AND t.year_class_id = t1.year_class_id ",
            "</when>",
            "WHERE t.id IS NOT NULL",
            "</script>"})
    public List<SenateScore> getSenateScoreTeamDetail(@Param("yearPart") Integer yearPart,
                                                      @Param("quarterNum") Integer quarterNum,
                                                      @Param("startLessonNum") Integer startLessonNum,
                                                      @Param("endLessonNum") Integer endLessonNum,
                                                      @Param("teacherId") Long teacherId,
                                                      @Param("subjectsId") String subjectsId,
                                                      @Param("projectsId") String projectsId,
                                                      @Param("detailType") Integer detailType,
                                                      @Param("startDate") String startDate,
                                                      @Param("endDate") String endDate,
                                                      @Param("senateDepartId") String senateDepartId,
                                                      @Param("trackType") String trackType,
                                                      @Param("teamId") String teamId,
                                                      @Param("yearClassId") String yearClassId
    );

}
